import cn.hutool.core.convert.Convert;
import cn.hutool.core.io.FileUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;

import java.util.ArrayList;
import java.util.List;

public class tmall {
    public static void main(String[] args) {
        //第一步，打开Excel文件
        ExcelReader reader = ExcelUtil.getReader(FileUtil.file("e:/tm_in.xlsx"));
        List<List<Object>> readAll = reader.read();

        //第二步，精简数据
        readAll = delUnnecessary(readAll);

        //第三步，数据处理
        readAll = dataProcessing(readAll);

        //第四步，保存文件
        ExcelWriter writer = ExcelUtil.getWriter("e:/tm_out.xlsx");
        writer.write(readAll);
        writer.close();
    }

    /**
     * 精简导出表单数据
     *
     * @param readAll 原始表单数据
     * @return 精简后数据
     */
    private static List<List<Object>> delUnnecessary(List<List<Object>> readAll) {
        List<List<Object>> necessary = new ArrayList<List<Object>>();
        for (int i = 0; i < readAll.size(); i++) {
            List<Object> row = new ArrayList<Object>();
            row.add(readAll.get(i).get(0)); //订单编号
            row.add(readAll.get(i).get(1)); //标题
            row.add(readAll.get(i).get(2)); //价格
            row.add(readAll.get(i).get(3)); //购买数量
            if (i == 0) {
                row.add("金额");            //金额
            }else {
                row.add(Convert.toDouble(readAll.get(i).get(2)) * Convert.toDouble(readAll.get(i).get(3)));
            }
            row.add(readAll.get(i).get(5)); //商品属性
            row.add(readAll.get(i).get(8)); //订单状态
            necessary.add(row);
        }
        return necessary;
    }

    /**
     * 数据处理
     *
     * @param readAll 原始数据
     * @return 处理后数据
     */
    private static List<List<Object>> dataProcessing(List<List<Object>> readAll) {
        for (int i = 1; i < readAll.size() - 1; i++) {
            if (readAll.get(i).get(0).equals(readAll.get(i + 1).get(0))) {
                readAll.get(i+1).set(4, Convert.toDouble(readAll.get(i).get(4)) + Convert.toDouble(readAll.get(i+1).get(4)));
                readAll.get(i).set(4, null);
            }
        }
        return readAll;
    }
}
